package com.example.demo.controller;

import com.example.demo.pojo.DataTables;
import com.example.demo.pojo.TableConstraint;
import com.example.demo.pojo.TableInfo;
import com.example.demo.service.DataTablesService;
import com.example.demo.vo.BaseResult;
import com.example.demo.vo.DataSourceVO;
import com.example.demo.vo.TableDataVo;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class DataTablesController {

    @Autowired
    private DataTablesService dataTablesService;

    @RequestMapping("/exportExcel")
    public BaseResult<String> exportExcel(@RequestBody TableDataVo dataVo)  {
        DataSourceVO vo = new DataSourceVO();
        vo.setDriverClassName("com.mysql.cj.jdbc.Driver");
        vo.setUrl("jdbc:mysql://"+dataVo.getIp()+":"+dataVo.getPort()+"/"+dataVo.getDbName()+"?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC");
        vo.setUsername(dataVo.getUserName());
        vo.setPassword(dataVo.getPassword());
        dataTablesService.changeDataSource(vo);
        if (dataVo.getTableList() == null || dataVo.getTableList().size() == 0) {
            dataVo.setTableList(dataTablesService.findTableNameByDbName(dataVo.getDbName()));
        }
        return BaseResult.success("", exportExcel(dataVo.getDbName(), dataVo.getTableList()));
    }

    private String exportExcel(String dbName, List<String> list) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            //数据库清单页单独创建
            XSSFSheet tableSheet = workbook.createSheet("数据库表清单");
            XSSFCellStyle style8 = workbook.createCellStyle();
            XSSFCellStyle style9 = workbook.createCellStyle();
            //创建字体设置为等线,大小为9
            XSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 9);
            font.setFontName("等线");
            XSSFFont font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 9);
            font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            font2.setFontName("等线");
            //创建字体设置为等线,大小为10
            XSSFFont font1 = workbook.createFont();
            font1.setFontHeightInPoints((short) 10);
            font1.setFontName("等线");
            //加粗
            font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            //创建样式
            XSSFCellStyle style = workbook.createCellStyle();
            /*XSSFFont font10 = workbook.createFont();
            font10.setFontHeightInPoints((short) 10);
            font10.setFontName("等线");*/
            style9.setFont(font);
            //设置样式1.有背景图
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            //自定义颜色
            XSSFColor aColor = new XSSFColor();
            byte[] by = {(byte) 180, (byte) 240, (byte) 240};
            aColor.setRGB(by);
            //设置背景色
            style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            //字体
            style.setFont(font2);
            //样式2，无背景图
            XSSFCellStyle style1 = workbook.createCellStyle();
            style1.setBorderTop(BorderStyle.THIN);
            style1.setBorderBottom(BorderStyle.THIN);
            style1.setBorderLeft(BorderStyle.THIN);
            style1.setBorderRight(BorderStyle.THIN);
            style1.setFont(font);
            //样式3，设置字体为10
            XSSFCellStyle style2 = workbook.createCellStyle();
            style2.setFont(font1);
            //样式4,上边框加粗
            XSSFCellStyle style3 = workbook.createCellStyle();
            style3.setBorderTop(BorderStyle.THIN);
            style3.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
            style3.setBorderBottom(BorderStyle.THIN);
            style3.setBorderLeft(BorderStyle.THIN);
            style3.setBorderRight(BorderStyle.THIN);
            style3.setFillForegroundColor(aColor);
            style3.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style3.setFont(font);
            //样式5,下边框加粗
            XSSFCellStyle style4 = workbook.createCellStyle();
            style4.setBorderTop(BorderStyle.THIN);
            style4.setBorderBottom(BorderStyle.THIN);
            style4.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
            style4.setBorderLeft(BorderStyle.THIN);
            style4.setBorderRight(BorderStyle.THIN);
            style4.setFont(font);
            //样式6,右边框加粗
            XSSFCellStyle style5 = workbook.createCellStyle();
            style5.setBorderTop(BorderStyle.THIN);
            style5.setBorderBottom(BorderStyle.THIN);
            style5.setBorderLeft(BorderStyle.THIN);
            style5.setBorderRight(BorderStyle.THIN);
            style5.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
            style5.setFont(font);
            //样式7,上边框和右边框加粗
            XSSFCellStyle style6 = workbook.createCellStyle();
            style6.setBorderTop(BorderStyle.THIN);
            style6.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
            style6.setBorderBottom(BorderStyle.THIN);
            style6.setBorderLeft(BorderStyle.THIN);
            style6.setBorderRight(BorderStyle.THIN);
            style6.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
            style6.setFillForegroundColor(aColor);
            style6.setFillPattern(CellStyle.SOLID_FOREGROUND);
            style6.setFont(font);
            //样式8,下、右边框加粗
            XSSFCellStyle style7 = workbook.createCellStyle();
            style7.setBorderTop(BorderStyle.THIN);
            style7.setBorderBottom(BorderStyle.THIN);
            style7.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
            style7.setBorderLeft(BorderStyle.THIN);
            style7.setBorderRight(BorderStyle.THIN);
            style7.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
            style7.setFont(font);
            tableSheet.setColumnWidth(0, 8 * 256);
            tableSheet.setColumnWidth(1, 28 * 256);
            tableSheet.setColumnWidth(2, 41 * 256);
            tableSheet.setColumnWidth(3, 17 * 256);
            //创建第一行
            XSSFRow tableSheetRow1 = tableSheet.createRow(0);
            //创建单元格
            XSSFCell tableSheetRow1Cell1 = tableSheetRow1.createCell(0);
            //设置第一行第一格的值
            tableSheetRow1Cell1.setCellValue("数据库清单");
            //设置大小
            style8.setFont(font1);
            tableSheetRow1Cell1.setCellStyle(style8);
            //创建第二行
            XSSFRow tableSheetRow2 = tableSheet.createRow(1);
            //创建第二行第一单元格
            XSSFCell tableSheetRow2Cell1 = tableSheetRow2.createCell(0);
            //创建第二行第二单元格
            XSSFCell tableSheetRow2Cell2 = tableSheetRow2.createCell(1);
            //创建第二行第三单元格
            XSSFCell tableSheetRow2Cell3 = tableSheetRow2.createCell(2);
            //创建第二行第四单元格
            XSSFCell tableSheetRow2Cell4 = tableSheetRow2.createCell(3);
            tableSheetRow2Cell1.setCellValue("NO");
            tableSheetRow2Cell1.setCellStyle(style);
            tableSheetRow2Cell2.setCellValue("数据库表业务名称");
            tableSheetRow2Cell2.setCellStyle(style);
            tableSheetRow2Cell3.setCellValue("数据库表物理名称");
            tableSheetRow2Cell3.setCellStyle(style);
            tableSheetRow2Cell4.setCellValue("备注");
            tableSheetRow2Cell4.setCellStyle(style);
            int sortNo = 2;
            int No = 1;
            int j = 1;
            for (String s : list) {
                Map<String, Object> prams = new HashMap<>();
                prams.put("tableName", s);
                prams.put("dbName", dbName);
                List<DataTables> dataTables = dataTablesService.findColumnDataByTableName(dbName, s);
                List<TableInfo> tableInfo = dataTablesService.findTableInfoByTableName(dbName, s);
                //创建工作表
                XSSFSheet sheet = workbook.createSheet();
                String comment = null;
                String name = null;
                for (TableInfo info : tableInfo) {
                    comment = info.getTableComment().replace("/", "或");
                    name = info.getTableName();
                    if (comment == null || comment.equals("") || comment.length() > 31) {
                        comment = name;
                    }
                    XSSFRow tableSheetRow = tableSheet.createRow(sortNo++);
                    XSSFCell cell0 = tableSheetRow.createCell(0);
                    cell0.setCellValue(No++);
                    cell0.setCellStyle(style9);
                    XSSFCell cell1 = tableSheetRow.createCell(1);
                    cell1.setCellValue(comment);
                    cell0.setCellStyle(style2);
                    XSSFCell cell2 = tableSheetRow.createCell(2);
                    cell2.setCellValue(name);
                    cell0.setCellStyle(style2);
                }
                workbook.setSheetName(j++, comment);
                //设置背景色
                //创建第一行
                XSSFRow row1 = sheet.createRow(0);
                //创建单元格
                XSSFCell cell = row1.createCell(1);
                //设置第一行第二格的值
                cell.setCellValue("数据库表情报");
                cell.setCellStyle(style2);
                //设置这几列的宽度
                sheet.setColumnWidth(0, 5 * 256);
                sheet.setColumnWidth(1, 20 * 256);
                sheet.setColumnWidth(2, 23 * 256);
                sheet.setColumnWidth(3, 16 * 256);
                sheet.setColumnWidth(6, 50 * 256);
                //创建第二行
                XSSFRow row = sheet.createRow(1);
                XSSFCell cell3 = row.createCell(1);
                cell3.setCellStyle(style);
                //设置第二行第二格的值为
                cell3.setCellValue("数据库表业务名称");
                style.setFillForegroundColor(aColor);
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                //合并单元格c2,d2
                XSSFCell cell7 = row.createCell(2);
                cell7.setCellValue(comment);
                CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 2, 3);
                sheet.addMergedRegion(cellRangeAddress);
                row.createCell(3).setCellStyle(style1);
                cell7.setCellStyle(style1);
                //合并单元格f2,g2
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));
                row.createCell(5).setCellStyle(style1);
                row.createCell(6).setCellStyle(style1);
                XSSFCell cell5 = row.createCell(4);
                style.setFillForegroundColor(aColor);
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cell5.setCellStyle(style);
                cell5.setCellValue("作成日");
                //创建第三行并设置值
                XSSFRow row3 = sheet.createRow(2);
                XSSFCell cell4 = row3.createCell(1);
                XSSFCell cell8 = row3.createCell(2);
                cell8.setCellValue(name);
                //合并单元格c3,d3
                sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
                cell8.setCellStyle(style1);
                row3.createCell(3).setCellStyle(style1);
                //合并单元格c5,d5
                sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
                row3.createCell(5).setCellStyle(style1);
                row3.createCell(6).setCellStyle(style1);
                cell4.setCellValue("数据库表物理名称");
                style.setFillForegroundColor(aColor);
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cell4.setCellStyle(style);
                XSSFCell cell6 = row3.createCell(4);
                style.setFillForegroundColor(aColor);
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cell6.setCellStyle(style);
                cell6.setCellValue("作成者");
                XSSFRow row6 = sheet.createRow(5);
                XSSFCell cell1 = row6.createCell(0);
                cell1.setCellStyle(style2);
                cell1.setCellValue("项目情报");
                XSSFRow row7 = sheet.createRow(6);
                XSSFCell cell2;
                String[] s1 = {"NO", "业务名", "物理名", "数据类型", "Not Null", "默认值"};
                cell2 = row7.createCell(6);
                cell2.setCellStyle(style6);
                cell2.setCellValue("备注");
                for (int i = 0; i < s1.length; i++) {
                    cell2 = row7.createCell(i);
                    cell2.setCellValue(s1[i]);
                    cell2.setCellStyle(style3);
                }
                int i = 7;
                for (DataTables dataTable : dataTables) {
                    row7 = sheet.createRow(i);
                    cell2 = row7.createCell(0);
                    cell2.setCellValue(dataTable.getOrdinalPosition());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(1);
                    cell2.setCellValue(dataTable.getColumnComment());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(2);
                    cell2.setCellValue(dataTable.getColumnName().toUpperCase());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(3);
                    cell2.setCellValue(dataTable.getColumnType());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(4);
                    cell2.setCellValue(dataTable.getIsNullAble());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(5);
                    cell2.setCellValue(dataTable.getColumnDefault());
                    cell2.setCellStyle(style1);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    cell2 = row7.createCell(6);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style5);
                    if (dataTables.size() == i - 7 + 1) {
                        cell2.setCellStyle(style4);
                    }
                    i++;
                }
                cell2.setCellStyle(style7);
                XSSFRow row4 = sheet.createRow(i + 1);
                XSSFCell cell9 = row4.createCell(0);
                cell9.setCellStyle(style2);
                cell9.setCellValue("制约信息");
                //固定的值
                String[] s2 = {"NO", "制约名称", "种类"};
                XSSFRow row5 = sheet.createRow(i + 2);
                cell2 = row5.createCell(3);
                cell2.setCellValue("制约定义");
                cell2.setCellStyle(style6);
                sheet.addMergedRegion(new CellRangeAddress(i + 2, i + 2, 3, 6));
                row5.createCell(4).setCellStyle(style6);
                row5.createCell(5).setCellStyle(style6);
                row5.createCell(6).setCellStyle(style6);
                for (int x = 0; x < s2.length; x++) {
                    cell2 = row5.createCell(x);
                    cell2.setCellValue(s2[x]);
                    cell2.setCellStyle(style3);
                }
                List<TableConstraint> constraint = dataTablesService.findConstraint(dbName, s);
                sheet.addMergedRegion(new CellRangeAddress(i + 3, i + 3, 3, 6));
                sheet.addMergedRegion(new CellRangeAddress(i + 4, i + 4, 3, 6));
                int m = i + 3;
                for (TableConstraint tableConstraint : constraint) {
                    row5 = sheet.createRow(m);
                    cell2 = row5.createCell(0);
                    cell2.setCellValue(dataTables.get(0).getOrdinalPosition());
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(1);
                    cell2.setCellValue(tableConstraint.getConstraintName());
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(2);
                    cell2.setCellValue(tableConstraint.getConstraintType());
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(3);
                    cell2.setCellValue(dataTables.get(0).getColumnName().toUpperCase());
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(4);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(5);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style1);

                    cell2 = row5.createCell(6);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style5);

                    m++;
                }
                //设置最后一行
                XSSFRow row2 = sheet.createRow(m);
                row2.createCell(0).setCellStyle(style4);
                row2.createCell(1).setCellStyle(style4);
                row2.createCell(2).setCellStyle(style4);
                row2.createCell(3).setCellStyle(style4);
                row2.createCell(4).setCellStyle(style4);
                row2.createCell(5).setCellStyle(style4);
                row2.createCell(6).setCellStyle(style7);
            }
            //转base64
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            return Base64.getEncoder().encodeToString(byteArrayOutputStream.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
}
